﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using Entity;

namespace DAO
{
    public class QuanLyKhachHangDAO
    {
        public static DataTable LayThongTinKhachHang()
        {
            SqlConnection connect = null;
            DataTable rs = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }
                String cmdText = "SELECT CMND,TenKH,DiaChi,SoDienThoai,Email,CapDo, NgayDangKy FROM KHACH_HANG_THAN_THIET a, LOAI_KHACH_HANG b " +
                                                    "WHERE a.MaLoaiKhachHang = b.MaLoaiKhachHang";
                rs = SqlHelper.ExecuteDataset(connect, CommandType.Text, cmdText).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (rs != null)
                    ((IDisposable)rs).Dispose();

                if (connect != null)
                    connect.Dispose();
            }
            return rs;
        }

        public static void CapNhatKhachHang(KhachHangThanThiet kh)
        {
            SqlConnection connect = null;
            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

                SqlParameter[] param = new SqlParameter[6];
                param[0] = new SqlParameter("@makh", kh.cmnd);
                param[1] = new SqlParameter("@tenkh", kh.tenkh);
                param[2] = new SqlParameter("@diachi", kh.diachi);
                param[3] = new SqlParameter("@sodienthoai", kh.sodienthoai);
                param[4] = new SqlParameter("@email", kh.email);
                param[5] = new SqlParameter("@maloaikh", kh.maloaikhachhang);
                String cmdText = "UPDATE KHACH_HANG_THAN_THIET SET TenKH=@tenkh,DiaChi=@diachi,SoDienThoai=@sodienthoai,Email=@email,MaLoaiKhachHang=@maloaikh " +
                                    "WHERE CMND = @makh";
                SqlHelper.ExecuteNonQuery(connect, CommandType.Text, cmdText, param);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            finally
            {
                if (connect != null)
                    connect.Dispose();
            }
        }

        public static void ThemKhachHang(KhachHangThanThiet kh)
        {
            SqlConnection connect = null;
            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

                SqlParameter[] param = new SqlParameter[7];
                param[0] = new SqlParameter("@cmnd", kh.cmnd);
                param[1] = new SqlParameter("@tenkh", kh.tenkh);
                param[2] = new SqlParameter("@diachi", kh.diachi);
                param[3] = new SqlParameter("@sodienthoai", kh.sodienthoai);
                param[4] = new SqlParameter("@email", kh.email);
                param[5] = new SqlParameter("@ngaydangky", kh.ngaydangky);
                param[6] = new SqlParameter("@maloaikh", kh.maloaikhachhang);
                String cmdText = "INSERT INTO KHACH_HANG_THAN_THIET VALUES " +
                                "(@tenkh, @cmnd, @diachi, @sodienthoai, @email, @ngaydangky, @maloaikh)";
                SqlHelper.ExecuteNonQuery(connect, CommandType.Text, cmdText, param);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            finally
            {
                if (connect != null)
                    connect.Dispose();
            }
        }

        public static void XoaKhachHang(String makh)
        {
            SqlConnection connect = null;
            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

                SqlParameter[] param = new SqlParameter[1];
                param[0] = new SqlParameter("@makh", makh);
                String cmdText = "DELETE KHACH_HANG_THAN_THIET WHERE CMND = @makh";
                SqlHelper.ExecuteNonQuery(connect, CommandType.Text, cmdText, param);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            finally
            {
                if (connect != null)
                    connect.Dispose();
            }
        }

        public static DataTable LayThongTinLoaiKH()
        {
            {
                SqlConnection connect = null;
                DataTable rs = null;

                try
                {
                    try
                    {
                        connect = Connection.GetConnection();
                    }
                    catch
                    {
                        throw new Exception("The connection with the database can´t be established");
                    }
                    String cmdText = "SELECT * FROM LOAI_KHACH_HANG";
                    rs = SqlHelper.ExecuteDataset(connect, CommandType.Text, cmdText).Tables[0];
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    if (rs != null)
                        ((IDisposable)rs).Dispose();

                    if (connect != null)
                        connect.Dispose();
                }

                return rs;
            }
        }

        public static void ThemLoaiKH(LoaiKhachHang loaikh)
        {
            SqlConnection connect = null;
            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

                SqlParameter[] param = new SqlParameter[2];
                param[0] = new SqlParameter("@capdo", loaikh.capdo);
                param[1] = new SqlParameter("@giamgia", loaikh.giamgia);
                String cmdText = "INSERT INTO LOAI_KHACH_HANG VALUES (@capdo,@giamgia)";
                SqlHelper.ExecuteNonQuery(connect, CommandType.Text, cmdText, param);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            finally
            {
                if (connect != null)
                    connect.Dispose();
            }
        }

        public static void CapNhatLoaiKH(LoaiKhachHang loaikh)
        {
            SqlConnection connect = null;
            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

                SqlParameter[] param = new SqlParameter[3];
                param[0] = new SqlParameter("@maloaikh", loaikh.maloaikhachhang);
                param[1] = new SqlParameter("@capdo", loaikh.capdo);
                param[2] = new SqlParameter("@giamgia", loaikh.giamgia);
                String cmdText = "UPDATE LOAI_KHACH_HANG SET CapDo = @capdo, GiamGia = @giamgia " +
                                    "WHERE MaLoaiKhachHang = @maloaikh";
                SqlHelper.ExecuteNonQuery(connect, CommandType.Text, cmdText, param);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            finally
            {
                if (connect != null)
                    connect.Dispose();
            }
        }

        public static Int32 LayMaLoaiKH(String capdo)
        {
            SqlConnection connect = null;
            DataTable rs = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }
                SqlParameter[] param = new SqlParameter[1];
                param[0] = new SqlParameter("@capdo", capdo);
                String cmdText = "SELECT MaLoaiKhachHang FROM LOAI_KHACH_HANG WHERE CapDo = @capdo";
                rs = SqlHelper.ExecuteDataset(connect, CommandType.Text, cmdText,param).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (rs != null)
                    ((IDisposable)rs).Dispose();

                if (connect != null)
                    connect.Dispose();
            }
            return Int32.Parse(rs.Rows[0][0].ToString());
        }

        public static DataTable LayCapDo()
        {
            SqlConnection connect = null;
            DataTable rs = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }
                String cmdText = "SELECT CapDo FROM LOAI_KHACH_HANG";
                rs = SqlHelper.ExecuteDataset(connect, CommandType.Text, cmdText).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (rs != null)
                    ((IDisposable)rs).Dispose();

                if (connect != null)
                    connect.Dispose();
            }
            return rs;
        }

        public static DataTable KiemTraHoaDon(int mahd)
        {
            {
                SqlConnection connect = null;
                DataTable rs = null;

                try
                {
                    try
                    {
                        connect = Connection.GetConnection();
                    }
                    catch
                    {
                        throw new Exception("The connection with the database can´t be established");
                    }
                    SqlParameter[] param = new SqlParameter[2];
                    param[0] = new SqlParameter("@mahd", mahd);
                    param[1] = new SqlParameter("@ngay", DateTime.Now);
                    String cmdText = "SELECT TenKH,DiaChi,SoDienThoai,TongTien FROM PHIEU_XUAT_HANG WHERE MaPhieu = @mahd " +
                                                                "and datediff(month,NgayXuat,@ngay) >= 3 and TongTien >=100000000";
                    rs = SqlHelper.ExecuteDataset(connect, CommandType.Text, cmdText, param).Tables[0];
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    if (rs != null)
                        ((IDisposable)rs).Dispose();

                    if (connect != null)
                        connect.Dispose();
                }

                return rs;
            }
        }
    }
}
